﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace InsideDL.MISData
{
    public class DataInside:DataMatrix
    {
        #region I002BAL
        public object GetI002BALRS(string[] p_csvs, string p_path
            , string[] p_months)
        {
            string sql = GetI002BALSQL(p_csvs,p_months);
            return getCSVDataByRecordSet(sql, p_path);
        }

        protected string GetI002BALSQL(string[] p_csvs, string[] p_months)
        {
             return string.Format(@"
SELECT
    資料年月
    ,分行別
    ,幣別 
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'3',大版塊) AS 大版塊1
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'1',小版塊) AS 小版塊1
    ,科目    
    ,當月平均
FROM(
		SELECT 
			年度 AS 資料年月
			,分行別
			,幣別 
			, 大版塊
			,小版塊
			,科目
			,ROUND([{3} 月積數]/{2},2) AS 當月平均
		FROM {0} AS C
		WHERE (
			C.科目 LIKE '13%'
		OR
			C.科目 LIKE '1113%'
		OR 
			C.科目 LIKE '1144%'
		OR 
			C.科目 LIKE '2144%'
		OR 
			C.科目 LIKE '23%'
		OR 
			C.科目 LIKE '2123%'
        OR 
            C.科目 = '2741000000'
			)
		AND ( TRIM(C.分行別) NOT LIKE '56%'  AND TRIM(C.分行別) NOT LIKE '9%')

UNION ALL

		SELECT 
			 年度 AS 資料年月
			,分行別
			,幣別 
			,大版塊
			,小版塊
			,科目
			,ROUND([{3} 月積數]/{2},2) AS 當月平均

		FROM {1} AS C
		WHERE (
			C.科目 LIKE '13%'
		OR
			C.科目 LIKE '1113%'
		OR 
			C.科目 LIKE '1144%'
		OR 
			C.科目 LIKE '2144%'
		OR 
			C.科目 LIKE '23%'
		OR 
			C.科目 LIKE '2123%'
        OR 
            C.科目 = '2741000000'
        )
		AND (TRIM(C.分行別) NOT LIKE '56%' AND  TRIM(C.分行別) NOT LIKE '9%')
)A

", p_csvs[0], p_csvs[1], Utility.GetCurrentDay(p_csvs[0]), p_months[0]);
        }
        #endregion

        #region I002Profit
        public object GetI002ProfitRS(string p_csv, string p_path,string[] p_months)
        {
            string sql = GetI002ProfitSQL(p_csv,p_months);
            return getCSVDataByRecordSet(sql, p_path);
        }

        protected string GetI002ProfitSQL(string p_csv, string[] p_months)
        {
            string sql,v1;
            v1 = "{0} 月餘額]  - [{1} 月餘額";
            sql = @"SELECT  
    年度 AS 資料年月
    ,分行別
    ,幣別 
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'3',大版塊) AS 大版塊1
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'1',小版塊) AS 小版塊1
    ,科目    
    ,[{1}] AS 當月發生數
FROM {0}
WHERE 
    (科目 LIKE '4501%'
OR
    科目 LIKE '5501%')
AND (分行別 NOT LIKE '56%' AND 分行別 NOT LIKE '9%')";
            if (p_months[1].Equals("12"))
            {
                v1 = string.Format("{0} 月餘額", p_months[0]);
            }
            else
            {
                v1 = string.Format(v1, p_months[0], p_months[1]);
            }

            return string.Format(sql
               , p_csv, v1);
        }
        #endregion

        #region OBUBAL
        public object GetOBUBALRS(string[] p_csvs, string p_path,string[] p_months)
        {
            string sql = GetOBUBALSQL(p_csvs,p_months);
            return getCSVDataByRecordSet(sql, p_path);
        }

        protected string GetOBUBALSQL(string[] p_csvs,string[] p_months)
        {
            //string[] months = GetThisLastMonth(p_csvs[0]);
            return string.Format(@"
SELECT
    年度 AS 資料年月
    ,分行別
    ,幣別 
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'3',大版塊) AS 大版塊1
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'1',小版塊) AS 小版塊1
    ,IIF(分行別='960' AND 科目='2317000000','2311000000'
	    ,IIF(分行別='960' AND 科目='2327000000','2321000000'
		    ,IIF(分行別='962' AND 科目='2317000000','2311000000'
			    ,IIF(分行別='962' AND 科目='2327000000','2321000000'
				    ,IIF(分行別='963' AND 科目='2317000000','2311000000'
					    ,IIF(分行別='963' AND 科目='2327000000','2321000000',科目)
					)	
				)
			)	
		)
	) AS 科目1
    ,ROUND([{3} 月積數]/{2},2) AS 當月平均
FROM (
SELECT *
FROM {0} AS C
WHERE (
    C.科目 LIKE '13%'
OR
    C.科目 LIKE '1113%'
OR 
    C.科目 LIKE '1144%'
OR 
    C.科目 LIKE '2144%'
OR 
    C.科目 LIKE '23%'
OR 
    C.科目 LIKE '2123%'
OR 
    C.科目 = '2741000000'

)

AND (C.分行別  LIKE '56%' 
        OR C.分行別  LIKE '9%' )


UNION ALL

SELECT *
FROM {1} AS C
WHERE (
     C.科目 LIKE '13%'
OR
    C.科目 LIKE '1113%'
OR 
    C.科目 LIKE '1144%'
OR 
    C.科目 LIKE '2144%'
OR 
    C.科目 LIKE '23%'
OR 
    C.科目 LIKE '2123%'
OR 
    C.科目 = '2741000000'

)
AND (C.分行別  LIKE '56%' 
        OR C.分行別  LIKE '9%' )

)A
", p_csvs[0], p_csvs[1], Utility.GetCurrentDay(p_csvs[0]),p_months[0]);
        }
        #endregion

        #region OBUProfit
        public object GetOBUProfitRS(string p_csv, string p_path,string[] p_months)
        {
            string sql = GetOBUProfitSQL(p_csv,p_months);
            return getCSVDataByRecordSet(sql, p_path);
            //return GetCSVData(sql, p_path);
            
        }

        protected string GetOBUProfitSQL(string p_csv,string[] p_months)
        {
            //string[] months = GetThisLastMonth(p_csv);
            string sql,v1;
            v1 = "{0} 月餘額]  - [{1} 月餘額";
            sql = @"
SELECT 
    年度 AS 資料年月
    ,分行別
    ,幣別 
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'3',大版塊) AS 大版塊1
    ,IIF(TRIM(大版塊) = 'A' AND 小版塊='5' ,'1',小版塊) AS 小版塊1
    ,IIF(分行別='960' AND 科目='5501006000','5501005000'
	    ,IIF(分行別='960' AND 科目='5501008000','5501007000'
		    ,IIF(分行別='962' AND 科目='5501006000','5501005000'
			    ,IIF(分行別='962' AND 科目='5501008000','5501007000'
				    ,IIF(分行別='963' AND 科目='5501006000','5501005000'
					    ,IIF(分行別='963' AND 科目='5501008000','5501007000',科目)
					)	
				)
			)	
		)
	) AS 科目1

         
    ,[{1}] AS 當月發生數
FROM {0} AS A
WHERE 
   ( A.科目 LIKE '4501%' 
        OR
    A.科目 LIKE '5501%')
AND (A.分行別  LIKE '56%' 
        OR A.分行別  LIKE '9%' )";
            if (p_months[1].Equals("12"))
            {
                v1 = string.Format("{0} 月餘額", p_months[0]);
            }
            else
            {
                v1 = string.Format(v1, p_months[0], p_months[1]);
            }
            return string.Format(sql,p_csv,v1);
        }
        #endregion

        public string[] GetCSVFields(string[] p_csvs, string p_path)
        {
            int i = 1;
            string[] fields = { string.Empty, string.Empty, string.Empty };
            foreach (string csv in p_csvs)
            {
                string sql = string.Format("SELECT * FROM {0} WHERE 1=0", csv);
                System.Data.DataTable dt = getCSVData(sql, p_path);
                switch (i)
                {
                    case 1:
                        fields[0] = dt.Columns[6].ColumnName + "," + dt.Columns[7].ColumnName;
                        break;
                    case 2:
                        fields[1] = dt.Columns[6].ColumnName + "," + dt.Columns[7].ColumnName +
                            "," + dt.Columns[8].ColumnName;
                        break;
                    case 3:
                        fields[2] = dt.Columns[6].ColumnName + "," + dt.Columns[7].ColumnName +
                            "," + dt.Columns[8].ColumnName;

                        break;
                }
                i++;
            }
            return fields;
        }
       
    }
}
